package cn.com.shopec.erp.provideinterface.commo;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.com.shopec.erp.common.utils.MathUtil;
import cn.com.shopec.erp.provideinterface.entity.MonthInAndOutCollect;
import cn.com.shopec.erp.provideinterface.vo.MonthInAndOutCollectVo;

/**
 * 
 * @ClassName POExcelExport
 * @Description 采购单导出EXCEL
 * @author chengui
 * @date 2013-7-11 下午8:54:06
 */
public class MiaocExcelExport {
	
	/** 日志. */
	private static Logger log = Logger.getLogger(MiaocExcelExport.class);
	/** 工作簿. */
	private HSSFWorkbook workbook = null;
	/** 工作表. */
	private HSSFSheet sheet = null;
	/** 工作行. */
	private HSSFRow row = null;
	/** 单元 格. */
	private HSSFCell cell = null;
	/** 最大SHEET数量. */
	private static final int SHEET_MAX_COUNT = 65535;
	
	/**
	 * 
	 * @MethodName createWrite
	 * @Description 输出表格
	 * @author chengui
	 * @date 2013-7-12 上午9:27:32
	 * @param out
	 */
	public void createWrite(OutputStream out) {
		try {
			workbook.write(out);
		} catch (FileNotFoundException e) {
			log.error("DataExport createWrite FileNotFoundException:", e);
		} catch (IOException e) {
			log.error("DataExport createWrite IOException:", e);
		} finally {
			if(out != null) {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
					log.error("DataExport createWrite IOException:", e);
				}
			}
		}
	}
	
	/**
	 * 
	 * @MethodName createSheet
	 * @Description 创建表格
	 * @author chengui
	 * @date 2013-7-12 上午9:27:58
	 * @param poeVos
	 * @param outputStream
	 */
	public void createSheet(String yearOfMonth, List<MonthInAndOutCollect> miaocs,
			ServletOutputStream outputStream) {
		//默认为1
        int sheetNum = 1;
        int totalSize = miaocs.size()+20;

        //计算要创建的sheet个数
        if(totalSize % SHEET_MAX_COUNT > 0) {
            sheetNum = miaocs.size() / SHEET_MAX_COUNT + 1;
        } else {
            sheetNum = miaocs.size() / SHEET_MAX_COUNT;
        }
        
        workbook = new HSSFWorkbook();
        /**循环工作表**/
        for(int i = 0; i < sheetNum ; i++) {
    		//i ----------------工作表数量
        	sheet = workbook.createSheet(("sheet"+i));
        	
        	createFirstRow(yearOfMonth);
            createTitle();
            
            /**循环工作表的第一行**/
            for(int j=i*SHEET_MAX_COUNT; j<(i+1)*SHEET_MAX_COUNT; j++) {
            	if(j>totalSize) {
            		break;
            	} else {
            		/**初始化行对象**/
	        		if(j<miaocs.size()) {
	        			row = sheet.createRow(j + 2 - (i * SHEET_MAX_COUNT));
	        			setRowValue(miaocs.get(j), j);
	        		} else {
	        			break;
	        		}
            	}
            }
    	}
        
        createWrite(outputStream);
	}
	
	/**
	 * 
	 * @MethodName setRowValue
	 * @Description 设置行参数
	 * @author chengui
	 * @date 2013-7-12 上午9:28:47
	 * @param poExcelVo
	 * @param j
	 */
	private void setRowValue(MonthInAndOutCollect miaoc, int j) {
		if (null != miaoc) {
			cell = row.createCell(0);
			cell.setCellValue(miaoc.getWhNo());
			
			cell = row.createCell(1);
			cell.setCellValue(miaoc.getWhName());
			
			cell = row.createCell(2);
			cell.setCellValue(miaoc.getCommCat());
			
			cell = row.createCell(3);
			cell.setCellValue(miaoc.getSku());
			
			cell = row.createCell(4);
			cell.setCellValue(miaoc.getInternationalCode());
			
			cell = row.createCell(5);
			cell.setCellValue(miaoc.getCommName());
			
			cell = row.createCell(6);
			cell.setCellValue(miaoc.getCommSpec());
			
			cell = row.createCell(7);
			cell.setCellValue(miaoc.getSpecCode());
			
			cell = row.createCell(8);
			cell.setCellValue(miaoc.getCommUnit());
			
			cell = row.createCell(9);
			if (1 == miaoc.getProdType()) {
				cell.setCellValue("购销");
			} else if (2 == miaoc.getProdType()) {
				cell.setCellValue("代销");
			}
			
			cell = row.createCell(10);
			cell.setCellValue(miaoc.getProRate());
			
			cell = row.createCell(11);
			cell.setCellValue(miaoc.getMonthStartInOrOutQty());
			
			cell = row.createCell(12);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthStartInOrOutQty() ? 0 : (miaoc.getMonthStartTaxPrice() - ((miaoc.getMonthStartTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100))/miaoc.getMonthStartInOrOutQty()));
			
			cell = row.createCell(13);
			cell.setCellValue(MathUtil.twoDecimalPlaces(miaoc.getMonthStartTaxPrice() - ((miaoc.getMonthStartTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100)));
			
			cell = row.createCell(14);
			cell.setCellValue(MathUtil.twoDecimalPlaces((miaoc.getMonthStartTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100));
			
			cell = row.createCell(15);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthStartInOrOutQty() ? 0 : miaoc.getMonthStartTaxPrice() / miaoc.getMonthStartInOrOutQty()));
			
			cell = row.createCell(16);
			cell.setCellValue(miaoc.getMonthStartTaxPrice());
			
			cell = row.createCell(17);
			cell.setCellValue(miaoc.getMonthInQty());
			
			cell = row.createCell(18);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthInQty() ? 0 : (miaoc.getMonthInTaxPrice() - ((miaoc.getMonthInTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100))/miaoc.getMonthInQty()));
			
			cell = row.createCell(19);
			cell.setCellValue(MathUtil.twoDecimalPlaces(miaoc.getMonthInTaxPrice() - ((miaoc.getMonthInTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100)));
			
			cell = row.createCell(20);
			cell.setCellValue(MathUtil.twoDecimalPlaces((miaoc.getMonthInTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100));
			
			cell = row.createCell(21);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthInQty() ? 0 : miaoc.getMonthInTaxPrice() / miaoc.getMonthInQty()));
			
			cell = row.createCell(22);
			cell.setCellValue(miaoc.getMonthInTaxPrice());
			
			cell = row.createCell(23);
			cell.setCellValue(miaoc.getMonthOutQty());
			
			cell = row.createCell(24);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthOutQty() ? 0 : (miaoc.getMonthOutTaxPrice() - ((miaoc.getMonthOutTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100))/miaoc.getMonthOutQty()));
			
			cell = row.createCell(25);
			cell.setCellValue(MathUtil.twoDecimalPlaces(miaoc.getMonthOutTaxPrice() - ((miaoc.getMonthOutTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100)));
			
			cell = row.createCell(26);
			cell.setCellValue(MathUtil.twoDecimalPlaces((miaoc.getMonthOutTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100));
			
			cell = row.createCell(27);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthOutQty() ? 0 : miaoc.getMonthOutTaxPrice() / miaoc.getMonthOutQty()));
			
			cell = row.createCell(28);
			cell.setCellValue(miaoc.getMonthOutTaxPrice());
			
			cell = row.createCell(29);
			cell.setCellValue(miaoc.getMonthEndInOrOutQty());
			
			cell = row.createCell(30);
			cell.setCellValue(miaoc.getMonthEndInOrOutQty());
			
			cell = row.createCell(31);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthEndInOrOutQty() ? 0 : (miaoc.getMonthEndTaxPrice() - ((miaoc.getMonthEndTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100))/miaoc.getMonthEndInOrOutQty()));
			
			cell = row.createCell(32);
			cell.setCellValue(MathUtil.twoDecimalPlaces(miaoc.getMonthEndTaxPrice() - ((miaoc.getMonthEndTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100)));
			
			cell = row.createCell(33);
			cell.setCellValue(MathUtil.twoDecimalPlaces((miaoc.getMonthEndTaxPrice()/(1+ Double.valueOf(miaoc.getProRate()) / 100))*Double.valueOf(miaoc.getProRate()) / 100));
			
			cell = row.createCell(34);
			cell.setCellValue(MathUtil.twoDecimalPlaces(0 == miaoc.getMonthEndInOrOutQty() ? 0 : miaoc.getMonthEndTaxPrice() / miaoc.getMonthEndInOrOutQty()));
			
			cell = row.createCell(35);
			cell.setCellValue(miaoc.getMonthEndTaxPrice());
		}
	}
	
	/**
	 * 
	 * @MethodName createTitle
	 * @Description 设置标题
	 * @author chengui
	 * @date 2013-7-12 上午9:29:19
	 */
	private void createTitle() {
		HSSFCellStyle style = workbook.createCellStyle();
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		row = sheet.createRow(1);
	    row.setHeight((short) 400);
	    
	    String[] title = new String[]{"仓库编码","仓库名称","商品类别","商品编码（sku)","国际码","商品名称","商品规格","规格码","商品单位",
	    		"销售类型","适用税率%","期初结存数量","'期初不含税单价","期初不含税金额","税金","期初含税单价","期初含税金额","收入数量","不含税收入单价",
	    		"不含税收入金额","税金","含税收入单价","含税收入金额","发出数量","不含税发出单价","不含税发出金额","税金","含税发出单价",
	    		"含税发出金额","期末结存数量","期末可用库存","不含税结存单价","不含税结存金额","税金","含税结存单价","含税结存金额"};
	    System.out.println("title size:" + title.length);
	    for (int i=0; i<title.length; i++) {
	    	cell = row.createCell(i);
	    	cell.setCellStyle(style);
	    	cell.setCellValue(title[i]);
	    }
	}
	
	/**
	 * 
	 * @MethodName createFirstRow
	 * @Description 设置第一行
	 * @author chengui
	 * @date 2013-7-12 上午9:30:52
	 */
	private void createFirstRow(String yearOfMonth) {
	    CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 35);
	    sheet.addMergedRegion(cra);
	    row = sheet.createRow(0);
	    cell = row.createCell(0);
	    HSSFCellStyle style = workbook.createCellStyle();
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    HSSFFont font = workbook.createFont();
	    font.setFontHeight(HSSFFont.BOLDWEIGHT_NORMAL);
	    style.setFont(font);
	    cell.setCellStyle(style);
	    String[] yom = yearOfMonth.split("-");
	    cell.setCellValue(yom[0] + "年" + yom[1] + "月产品收发存汇总表");
	}
	
}
